﻿using System;
using System.Collections.ObjectModel;
using Oracle.DataAccess.Client;

namespace ISHotel.Database
{
    [Serializable]
    public class StravovaniTable : DbTable
    {
        public static String TABLE_NAME = "Stravovani";

        public String SQL_SELECT = "SELECT * FROM Stravovani ORDER BY id_stravovani ASC";
        public String SQL_SELECT_id_stravovani = "SELECT * FROM Stravovani WHERE id_stravovani=:id_stravovani";
        public String SQL_INSERT = "INSERT INTO Stravovani (nazev_stravovani, cena_stravovani) VALUES (:nazev_stravovani, :cena_stravovani)";
        public String SQL_UPDATE = "UPDATE Stravovani SET nazev_stravovani=:nazev_stravovani, cena_stravovani=:cena_stravovani WHERE id_stravovani=:id_stravovani";
        public String SQL_DELETE = "DELETE FROM Stravovani WHERE id_stravovani=:id_stravovani";

        public StravovaniTable()
            : base(TABLE_NAME)
        {
        }

        /**
 * Insert the record.
 **/
        public int Insert(Stravovani Stravovani)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_INSERT);
            PrepareCommand(command, Stravovani);
            int ret = mDatabase.Insert(command);
            mDatabase.Close();
            return ret;
        }

        /**
         * Update the record.
         **/
        public int Update(Stravovani Stravovani)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_UPDATE);

            command.Parameters.Add(new OracleParameter(":" + Stravovani.ATTR_nazev_stravovani, OracleDbType.Varchar2, Stravovani.nazev_stravovani.Length));
            command.Parameters[":" + Stravovani.ATTR_nazev_stravovani].Value = Stravovani.nazev_stravovani;

            command.Parameters.Add(new OracleParameter(":" + Stravovani.ATTR_cena_stravovani, OracleDbType.Decimal));
            command.Parameters[":" + Stravovani.ATTR_cena_stravovani].Value = Stravovani.cena_stravovani;

            command.Parameters.Add(new OracleParameter(":" + Stravovani.ATTR_id_stravovani, OracleDbType.Decimal));
            command.Parameters[":" + Stravovani.ATTR_id_stravovani].Value = Stravovani.id_stravovani;

            int ret = mDatabase.Update(command);
            mDatabase.Close();
            return ret;
        }

        /**
         * Prepare a command.
         **/

        private void PrepareCommand(OracleCommand command, Stravovani Stravovani)
        {
            command.Parameters.Add(new OracleParameter(":" + Stravovani.ATTR_nazev_stravovani, OracleDbType.Varchar2, Stravovani.nazev_stravovani.Length));
            command.Parameters[":" + Stravovani.ATTR_nazev_stravovani].Value = Stravovani.nazev_stravovani;

            command.Parameters.Add(new OracleParameter(":" + Stravovani.ATTR_cena_stravovani, OracleDbType.Decimal));
            command.Parameters[":" + Stravovani.ATTR_cena_stravovani].Value = Stravovani.cena_stravovani;
        }

        /**
         * Select records.
         **/
        public Collection<Stravovani> Select()
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT);
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Stravovani> Stravovanis = Read(reader);
            reader.Close();
            mDatabase.Close();
            return Stravovanis;
        }

        /**
         * Select the record.
         **/
        public Stravovani Select(int id_stravovani)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT_id_stravovani);

            command.Parameters.Add(new OracleParameter(":" + Stravovani.ATTR_id_stravovani, OracleDbType.Decimal));
            command.Parameters[":" + Stravovani.ATTR_id_stravovani].Value = id_stravovani;
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Stravovani> Stravovanis = Read(reader);
            Stravovani Stravovani1 = null;
            if (Stravovanis.Count == 1)
            {
                Stravovani1 = Stravovanis[0];
            }
            reader.Close();
            mDatabase.Close();
            return Stravovani1;
        }

        private Collection<Stravovani> Read(OracleDataReader reader)
        {
            Collection<Stravovani> Stravovanis = new Collection<Stravovani>();

            while (reader.Read())
            {
                Stravovani Stravovani = new Stravovani();
                Stravovani.id_stravovani = (int)reader.GetDecimal(0);
                Stravovani.nazev_stravovani = reader.GetString(1);
                Stravovani.cena_stravovani = (int)reader.GetDecimal(2);
                Stravovanis.Add(Stravovani);
            }
            return Stravovanis;
        }

        /**
         * Delete the record.
         */
        public int Delete(int id_stravovani)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_DELETE);

            command.Parameters.Add(new OracleParameter(":" + Stravovani.ATTR_id_stravovani, OracleDbType.Decimal));
            command.Parameters[":" + Stravovani.ATTR_id_stravovani].Value = id_stravovani;
            int ret;
            try
            {
                ret = mDatabase.Delete(command);
            }
            catch (Exception e)
            {
                throw e;
            }
            mDatabase.Close();
            return ret;
        }
    }
}